-- STANDARD SCRIPT
-- STEP 1 BELOW HAS TO BE CUSTOMIZED FOR EACH CLIENT 
--


--------------------------------------------------------------------------------------
-- FUNCTION TO LOAD FROM temp_batch_insurance2_t TO batch_insurance2_t DEDUPING BY UHC and MEDICA


\set ON_ERROR_STOP 1
CREATE OR REPLACE FUNCTION batch_insurance2_loading_f() RETURNS integer AS $$
DECLARE
  v_status integer;
  v_batch_run_date timestamp;
  v_cust_id integer;
  v_count integer;
BEGIN


SELECT INTO v_batch_run_date our_run_date from temp_batch_insurance2_t limit 1;
SELECT INTO v_cust_id cust_id from temp_batch_insurance2_t limit 1;
SELECT INTO v_count count(*) from temp_batch_insurance2_t;

PERFORM ml_upd_batch_status_insurance2_new_f(v_cust_id,v_batch_run_date,1,2,'batch_insurance2_t LOADING START');




-- STEP 1 - SCRATCH batch_insurance2_t 
-- 

TRUNCATE batch_insurance2_t;




-- STEP 2 - INSERTING INTO batch_insurance2_t 

RAISE NOTICE 'START LOADING batch_insurance2_t TABLE ';

INSERT INTO batch_insurance2_t
SELECT
    total.cust_id                   ,
    total.hid                       ,
    total.our_run_date              ,
    total.file_date                 ,
    total.individual_matchkey       ,
    total.acctnum                   ,
    total.service_date_begin        ,
    total.service_date_end          ,
    total.charges                   ,
    total.balance                   ,
    total.pat_proc_type             ,
    total.pat_type                  ,
    total.obs_ind                   ,
    total.output_id                 ,
    total.active_flag               ,
    (CASE WHEN (
                 (total.carrier_code <> '00024') 
                 OR 
                 ( total.customer_type = 'H' and total.pat_proc_type = 'I' and total.obs_ind <> 'Y') 
               ) 
               AND
               (
                 (total.effective_date IS NOT NULL AND total.service_date_begin < total.effective_date) 
                  OR
                 (total.effective_date_end IS NOT NULL AND total.service_date_begin > total.effective_date_end)
               )                                                                                                  THEN total.suppress_flag + 100
          WHEN (total.carrier_code <> '00024') 
               OR 
               ( total.customer_type = 'H' and total.pat_proc_type = 'I' and total.obs_ind <> 'Y')                THEN total.suppress_flag
          WHEN (
                 (total.carrier_code = '00024')
                 AND 
                 ( total.customer_type = 'H' and total.pat_proc_type = 'I' and total.obs_ind <> 'Y')
               )
               AND
               (
                 (total.effective_date IS NULL OR total.service_date_begin < total.effective_date)
                  OR
                 (total.effective_date_end IS NOT NULL AND total.service_date_begin > total.effective_date_end)
               )                                                                                                  THEN total.suppress_flag + 100
          WHEN (
                 (total.carrier_code = '00024')
                AND 
                 ( total.customer_type = 'H' and total.pat_proc_type = 'I' and total.obs_ind <> 'Y')
               )                                                                                                  THEN total.suppress_flag
          WHEN (insurance_desc2 <> 'MEDICARE B')
               OR
               (
                 (total.effective_date2 IS NULL OR total.service_date_begin < total.effective_date2)
                  OR
                 (total.effective_date_end2 IS NOT NULL AND total.service_date_begin > total.effective_date_end2)
               )                                                                                                   THEN total.suppress_flag + 100
          ELSE                                                                                                          total.suppress_flag
          END) as suppress_flag     ,
    total.processing_ssn_flag ,
    (CASE WHEN total.ssn IS NOT NULL AND total.patient_ssn IS NOT NULL THEN
    		(substring(total.ssn from 1 for 1) = substring(total.patient_ssn from 1 for 1))::integer +
                (substring(total.ssn from 2 for 1) = substring(total.patient_ssn from 2 for 1))::integer +
                (substring(total.ssn from 3 for 1) = substring(total.patient_ssn from 3 for 1))::integer +
                (substring(total.ssn from 4 for 1) = substring(total.patient_ssn from 4 for 1))::integer +
                (substring(total.ssn from 5 for 1) = substring(total.patient_ssn from 5 for 1))::integer +
                (substring(total.ssn from 6 for 1) = substring(total.patient_ssn from 6 for 1))::integer +
                (substring(total.ssn from 7 for 1) = substring(total.patient_ssn from 7 for 1))::integer +
                (substring(total.ssn from 8 for 1) = substring(total.patient_ssn from 8 for 1))::integer +
                (substring(total.ssn from 9 for 1) = substring(total.patient_ssn from 9 for 1))::integer 
          ELSE NULL
          END) as quality,
    total.search_pattern,
    total.emdeon_ins_type           ,
    total.medlytix_ins_type         ,
    (CASE WHEN total.lname IS NOT NULL AND total.lname <> total.patient_lname THEN 'Y'
          ELSE                                                                     'N'
          END) as flag_mismatch_lname,
    (CASE WHEN total.fname IS NOT NULL AND total.fname <> total.patient_fname THEN 'Y'
          ELSE                                                                     'N'
          END) as flag_mismatch_fname,
    (CASE WHEN total.ssn IS NOT NULL AND total.ssn <> total.patient_ssn     THEN 'Y'
          ELSE                                                                   'N'
          END) as flag_mismatch_ssn ,
    (CASE WHEN total.dob IS NOT NULL AND total.dob <> total.patient_dob       THEN 'Y'
          ELSE                                                                     'N'
          END) as flag_mismatch_dob ,
    total.name_type                 ,
    total.lname                     ,
    total.mname                     ,
    total.fname                     ,
    total.addr1                     ,
    total.city                      ,
    total.state                     ,
    total.ssn                       ,
    total.dob                       ,
    total.gender                    ,
    total.patient_lname             ,
    total.patient_mname             ,
    total.patient_fname             ,
    total.patient_addr1             ,
    total.patient_addr2             ,
    total.patient_city              ,
    total.patient_state             ,
    total.patient_zip               ,
    total.patient_ssn               ,
    total.patient_dob               ,
    total.patient_gender            ,
    total.subscriber_lname          ,
    total.subscriber_mname          ,
    total.subscriber_fname          ,
    total.subscriber_addr1          ,
    total.subscriber_addr2          ,
    total.subscriber_city           ,
    total.subscriber_state          ,
    total.subscriber_zip            ,
    total.subscriber_ssn            ,
    total.subscriber_dob            ,
    total.subscriber_id             ,
    total.carrier_code              ,
    total.insurance_desc            ,
    total.insurance_contact_name    ,
    total.insurance_contact_phone   ,
    total.insurance_addr            ,
    total.insurance_city            ,
    total.insurance_state           ,
    total.insurance_zip             ,
    total.group_number              ,
    total.plan_type                 ,
    total.plan_number               ,
    total.network_id                ,
    total.effective_date            ,
    total.effective_date_end        ,
    (CASE WHEN carrier_code <> '00024'                                                                         THEN total.c_insurance_desc1
          WHEN total.c_insurance_desc1 IS NOT NULL                 AND
               total.c_effective_date1 IS NOT NULL                 AND
               total.service_date_begin >= total.c_effective_date1 AND
               (total.c_effective_date_end1 IS NULL OR total.service_date_begin < total.c_effective_date_end1) THEN total.c_insurance_desc1
          WHEN total.c_insurance_desc2 IS NOT NULL                 AND
               total.c_effective_date2 IS NOT NULL                 AND
               total.service_date_begin >= total.c_effective_date2 AND
               (total.c_effective_date_end2 IS NULL OR total.service_date_begin < total.c_effective_date_end2) THEN total.c_insurance_desc2
          ELSE                                                                                                      'XXXXX'
          END) as c_insurance_desc  ,
    (CASE WHEN carrier_code <> '00024'                                                                          THEN total.c_subscriber_id1
          WHEN total.c_insurance_desc1  IS NOT NULL                AND
               total.c_effective_date1  IS NOT NULL                AND
               total.service_date_begin >= total.c_effective_date1 AND
               (total.c_effective_date_end1 IS NULL OR total.service_date_begin < total.c_effective_date_end1) THEN total.c_subscriber_id1
          WHEN total.c_insurance_desc2  IS NOT NULL                AND
               total.c_effective_date2  IS NOT NULL                AND
               total.service_date_begin >= total.c_effective_date2 AND
               (total.c_effective_date_end2 IS NULL OR total.service_date_begin < total.c_effective_date_end2) THEN total.c_subscriber_id2
          ELSE                                                                                                      NULL 
          END) as c_subscriber_id  ,
    (CASE WHEN carrier_code <>  '00024'                                                                         THEN total.c_effective_date1
          WHEN total.c_insurance_desc1  IS NOT NULL                AND
               total.c_effective_date1  IS NOT NULL                AND
               total.service_date_begin >= total.c_effective_date1 AND
               (total.c_effective_date_end1 IS NULL OR total.service_date_begin < total.c_effective_date_end1) THEN total.c_effective_date1
          WHEN total.c_insurance_desc2  IS NOT NULL                AND
               total.c_effective_date2  IS NOT NULL                AND
               total.service_date_begin >= total.c_effective_date2 AND
               (total.c_effective_date_end2 IS NULL OR total.service_date_begin < total.c_effective_date_end2) THEN total.c_effective_date2
          ELSE                                                                                                      NULL 
          END) as c_effective_date  ,
    (CASE WHEN carrier_code <> '00024'                                                                          THEN total.c_effective_date_end1
          WHEN total.c_insurance_desc1  IS NOT NULL                AND
               total.c_effective_date1  IS NOT NULL                AND
               total.service_date_begin >= total.c_effective_date1 AND
               (total.c_effective_date_end1 IS NULL OR total.service_date_begin < total.c_effective_date_end1) THEN total.c_effective_date_end1
          WHEN total.c_insurance_desc2  IS NOT NULL                AND
               total.c_effective_date2  IS NOT NULL                AND
               total.service_date_begin >= total.c_effective_date2 AND
               (total.c_effective_date_end2 IS NULL OR total.service_date_begin < total.c_effective_date_end2) THEN total.c_effective_date_end2
          ELSE                                                                                                      NULL
          END) as c_effective_date_end,
    (CASE WHEN carrier_code <> '00024'                                                                          THEN total.c_contact_phone1
          WHEN total.c_insurance_desc1  IS NOT NULL                AND
               total.c_effective_date1  IS NOT NULL                AND
               total.service_date_begin >= total.c_effective_date1 AND
               (total.c_effective_date_end1 IS NULL OR total.service_date_begin < total.c_effective_date_end1) THEN total.c_contact_phone1
          WHEN total.c_insurance_desc2  IS NOT NULL                AND
               total.c_effective_date2  IS NOT NULL                AND
               total.service_date_begin >= total.c_effective_date2 AND
               (total.c_effective_date_end2 IS NULL OR total.service_date_begin < total.c_effective_date_end2) THEN total.c_contact_phone2
          ELSE                                                                                                      NULL
          END) as c_contact_phone,
    total.insurance_desc2           ,
    total.subscriber_id2            ,
    total.effective_date2           ,
    total.effective_date_end2       ,
    total.insurance_contact_phone2  ,
    total.insurance_desc3           ,
    total.subscriber_id3            ,
    total.effective_date3           ,
    total.effective_date_end3       ,
    total.insurance_contact_phone3  ,
    total.client_field1             ,
    total.client_field2             ,
    total.client_field3             ,
    total.client_date1              

FROM

 (SELECT
   emdeon.*,
   treatment.customer_type 

 FROM

   (SELECT
      batch.cust_id                   , 
      batch.our_run_date              ,
      ins.individual_matchkey         ,
      batch.file_date                 ,
      batch.active_flag               ,
      batch.suppress_flag             ,
      ins.processing_ssn_flag         ,
      batch.quality                   ,
      batch.search_pattern            ,
      batch.emdeon_ins_type           ,
      batch.medlytix_ins_type         ,
      batch.patient_lname             ,
      batch.patient_mname             ,
      batch.patient_fname             ,
      batch.patient_addr1             ,
      batch.patient_addr2             ,
      batch.patient_city              ,
      batch.patient_state             ,
      batch.patient_zip               ,
      batch.patient_ssn               ,
      batch.patient_dob               ,
      batch.patient_gender            ,
      batch.subscriber_lname          ,
      batch.subscriber_mname          ,
      batch.subscriber_fname          ,
      batch.subscriber_addr1          ,
      batch.subscriber_addr2          ,
      batch.subscriber_city           ,
      batch.subscriber_state          ,
      batch.subscriber_zip            ,
      batch.subscriber_ssn            ,
      batch.subscriber_dob            ,
      batch.subscriber_id             ,
      batch.carrier_code              ,
      batch.insurance_desc            ,
      batch.insurance_contact_name    ,
      batch.insurance_contact_phone   ,
      batch.insurance_addr            ,
      batch.insurance_city            ,
      batch.insurance_state           ,
      batch.insurance_zip             ,
      batch.group_number              ,
      batch.plan_type                 ,
      batch.plan_number               ,
      batch.network_id                ,
      batch.effective_date            ,
      batch.effective_date_end        ,
      batch.c_insurance_desc1         ,
      batch.c_subscriber_id1          ,
      batch.c_effective_date1         ,
      batch.c_effective_date_end1     ,
      batch.c_contact_phone1          ,
      batch.c_insurance_desc2         ,
      batch.c_subscriber_id2          ,
      batch.c_effective_date2         ,
      batch.c_effective_date_end2     ,
      batch.c_contact_phone2          ,
      batch.insurance_desc2           ,
      batch.subscriber_id2            ,
      batch.effective_date2           ,
      batch.effective_date_end2       ,
      batch.insurance_contact_phone2  ,
      batch.insurance_desc3           ,
      batch.subscriber_id3            ,
      batch.effective_date3           ,
      batch.effective_date_end3       ,
      batch.insurance_contact_phone3  ,
      ins.treatment_code,
      ins.hid ,
      ins.acctnum ,
      ins.service_date_begin,
      ins.service_date_end,
      batch.name_type as name_type,
      (CASE WHEN batch.name_tp = 'G' THEN ins.g_lname
            ELSE                          ins.lname
            END) as lname,
      (CASE WHEN batch.name_tp = 'G'  THEN ins.g_mname
            ELSE                           ins.mname
            END) as mname,
      (CASE WHEN batch.name_tp = 'G'  THEN ins.g_fname
            ELSE                           ins.fname
            END) as fname,
      (CASE WHEN batch.name_tp = 'G'  THEN ins.g_gender
            ELSE                           ins.gender
            END) as gender,
      (CASE WHEN batch.name_tp = 'G'  THEN ins.g_ssn
            ELSE                           ins.ssn 
            END) as ssn,
      (CASE WHEN batch.name_tp = 'G'  THEN ins.g_dob
            ELSE                           ins.dob 
            END) as dob,
      (CASE WHEN batch.name_tp = 'G'  THEN ins.g_addr1
            ELSE                           ins.addr1
            END) as addr1,
      (CASE WHEN batch.name_tp = 'G'  THEN ins.g_city
            ELSE                           ins.city 
            END) as city,
      (CASE WHEN batch.name_tp = 'G'  THEN ins.g_state
            ELSE                           ins.state
            END) as state,
      ins.charges,
      ins.balance,
      ins.pat_proc_type,
      ins.pat_type,
      ins.obs_ind,
      ins.output_id,
      ins.emdeon_tpl_flag,
      ins.emdeon_commercial_flag,
      ins.emdeon_medicare_flag,
      ins.emdeon_medicaid_flag,
      ins.client_field1,
      ins.client_field2,
      ins.client_field3,
      ins.client_date1


    FROM

   		(SELECT
      			distinct on (a.emdeon_matchkey,a.cl) a.*
    		FROM
   			(SELECT *,
			        substring(emdeon_matchkey from 3) as insurance2_matchkey,
				substring(emdeon_matchkey from 1 for 1) as name_tp,
           			(CASE WHEN carrier_code = '00029' THEN '0'::varchar(76)
                      	              WHEN carrier_code = '00196' THEN '0'::varchar(76)
                                      ELSE                             '1'||insurance_desc::varchar(76)
                                      END) as cl
                         FROM
                              temp_batch_insurance2_t
                        ) as a

    		ORDER BY
      		  a.emdeon_matchkey,
      		  a.cl,
      		  a.active_flag desc,
      		  a.insurance_desc desc
    
   		) as batch 

                JOIN

                (SELECT 
                        hid||'-'||acctnum as insurance2_matchkey,
      			cust_id                   ,
                        treatment_code            ,
      			hid                       ,
      			our_run_date              ,
      			individual_matchkey       ,
			processing_ssn_flag       ,
      			acctnum                   ,
      			service_date_begin        ,
      			service_date_end          ,
      			lname                     ,
      			mname                     ,
      			fname                     ,
                        gender                    ,
      			ssn                       ,
      			dob                       ,
                        addr1                     ,
      			city                      ,
      			state                     ,
                        g_lname                   ,
                        g_mname                   ,
                        g_fname                   ,
                        g_gender                  ,
                        g_ssn                     ,
                        g_dob                     ,
                        g_addr1                   ,
                        g_city                    ,
                        g_state                   ,
      			charges                   ,
      			balance                   ,
      			pat_proc_type             ,
      			pat_type                  ,
      			obs_ind                   ,
                        output_id                 ,
      			emdeon_tpl_flag           ,
      			emdeon_commercial_flag    ,
      			emdeon_medicare_flag      ,
      			emdeon_medicaid_flag      ,
      			client_field1             ,
      			client_field2             ,
      			client_field3             ,
     		        client_date1

                 FROM
                        insurance1_t

                ) as ins

                ON batch.cust_id = ins.cust_id AND batch.our_run_date = ins.our_run_date AND batch.insurance2_matchkey = ins.insurance2_matchkey


   ) as emdeon

   JOIN

   (SELECT
      cust_id                   ,
      treatment_code            ,
      customer_type             

    FROM
      insurance0_treatment_t

    ) as treatment 

    ON emdeon.cust_id = treatment.cust_id AND emdeon.treatment_code = treatment.treatment_code 
 
 ) as total
;

GET DIAGNOSTICS v_status = ROW_COUNT;
RAISE NOTICE 'FINISH LOADING batch_insurance2_t TABLE : %', v_status;




-- STEP 2 UPDATE batch_status_insurance2_t WITH THE NUMBER INSERTED

  UPDATE batch_status_insurance2_t
    SET
      temp_batch_insurance2_received = v_count,
      batch_insurance2_inserted = v_status
    WHERE
      (batch_status_insurance2_t.last_run_date = v_batch_run_date and batch_status_insurance2_t.cust_id = v_cust_id);

PERFORM ml_upd_batch_status_insurance2_new_f(v_cust_id,v_batch_run_date,1,2,'batch_insurance2_t LOADING END');



  return v_status;
END;
$$ LANGUAGE plpgsql;
